package com.shketai.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.shketai.entity.Attence;
import com.shketai.entity.Classinfo;
import com.shketai.entity.Info;
import com.shketai.entity.Reasons;
import com.shketai.entity.Student;

public class AttenceDao {

	private SessionFactory sessionFactory;

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	/**
	 * 查看考勤
	 * 
	 * @param id
	 * @return
	 */
	public List<Map<String, Object>> findAttenceById(int user_id) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Session session = sessionFactory.getCurrentSession();
		Query query1 = session.createSQLQuery("select a.name,c.total from "
				+ "(select b.id as sid,b.stuName as name from student b where b.user_id = ?) a left join (select d.stu_id as sid,count(*) as total from attence d where d.attence_status = 1 group by d.stu_id) c on a.sid = c.sid");
		query1.setLong(0, user_id);
		List<Object[]> list1 = query1.list();

		Query query2 = session.createSQLQuery("select a.name,c.total from "
				+ "(select b.id as sid,b.stuName as name from student b where b.user_id = ?) a left join (select d.stu_id as sid,count(*) as total from attence d where d.attence_status = 2 group by d.stu_id) c on a.sid = c.sid");
		query2.setLong(0, user_id);
		List<Object[]> list2 = query2.list();

		Query query3 = session.createSQLQuery("select a.name,c.total from "
				+ "(select b.id as sid,b.stuName as name from student b where b.user_id = ?) a left join (select d.stu_id as sid,count(*) as total from attence d where d.attence_status = 3 group by d.stu_id) c on a.sid = c.sid");
		query3.setLong(0, user_id);
		List<Object[]> list3 = query3.list();

		Query query4 = session.createSQLQuery("select a.name,c.total from "
				+ "(select b.id as sid,b.stuName as name from student b where b.user_id = ?) a left join (select d.stu_id as sid,count(*) as total from attence d where d.attence_status = 4 group by d.stu_id) c on a.sid = c.sid");
		query4.setLong(0, user_id);
		List<Object[]> list4 = query4.list();

		for (int i = 0; i < list1.size(); i++) {
			Map<String, Object> allAttence = new HashMap<String, Object>();
			HashMap<Integer, Integer> map = new HashMap<Integer, Integer>();

			Integer i1 = (list1.get(i)[1] == null ? 0 : Integer.parseInt(list1.get(i)[1].toString()));
			Integer i2 = (list2.get(i)[1] == null ? 0 : Integer.parseInt(list2.get(i)[1].toString()));
			Integer i3 = (list3.get(i)[1] == null ? 0 : Integer.parseInt(list3.get(i)[1].toString()));
			Integer i4 = (list4.get(i)[1] == null ? 0 : Integer.parseInt(list4.get(i)[1].toString()));

			map.put(1, i1);
			map.put(2, i2);
			map.put(3, i3);
			map.put(4, i4);
			allAttence.put("stuName", list1.get(i)[0].toString());
			allAttence.put("attence", map);
			list.add(allAttence);
		}
		return list;
	}

	public List<Attence> UnAttence(int user_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where user_id =:user_id");
		query.setInteger("user_id", user_id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Attence where attence_status=2 and student.id in(:ids) ");
		query2.setParameterList("ids",ids);
		List<Attence> list1 = query2.list();
		return list1;
	}
	/**
	 * 考勤
	 * 
	 * @param id
	 */
	public void add(List<Integer> stu_id, int attence_status, int classinfo_id, int reason_id) {
		Session session = sessionFactory.getCurrentSession();
		Date now = new Date();
		// 遍历集合，得到 每个学生id
		for (Integer id : stu_id) {
			Attence attence = new Attence();
			Student student = new Student();
			student.setId(id);
			attence.setStudent(student);
			Classinfo classinfo = new Classinfo();
			classinfo.setId(classinfo_id);
			attence.setAttence_status(attence_status);
			attence.setAttence_time(now);
			attence.setClassinfo(classinfo);
			if(reason_id!=0){
				Reasons reasons = new Reasons();
				reasons.setId(reason_id);
				attence.setReasons(reasons);
			}
			session.save(attence);
		}
	}

	/**
	 * 考勤统计
	 * 
	 * @return
	 */
	public List<Attence> CountAttence() {
		String hql = "from Attence";
		Session session = sessionFactory.getCurrentSession();
		List<Attence> list = session.createQuery(hql).list();
		return list;
	}

	public List<Object[]> Attendancesummary(int page, int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select s.stuName,c.classname,s.personalNum,a.attence_status ,r.reason from attence a INNER JOIN classinfo c ON a.classinfo_id=c.id AND  a.attence_time>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)  AND a.classinfo_id=? INNER JOIN student s ON a.stu_id=s.id LEFT JOIN reasons r ON a.reason_id=r.id   ");
		query.setInteger(0, classinfo_id);
		query.setFirstResult((page - 1) * 10);
		query.setMaxResults(10);
		List<Object[]> list = query.list();
		return list;
	}
	
	public int findCount(int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select count(distinct stu_id) from attence  where  classinfo_id=? AND attence_time>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)");
		query.setInteger(0, classinfo_id);
		BigInteger total = (BigInteger) query.uniqueResult();
		return total.intValue();
	}

	public List<Object[]> Attendancesummary1(int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select s.stuName,c.classname,s.personalNum,a.attence_status ,r.reason from attence a INNER JOIN classinfo c ON a.classinfo_id=c.id AND  a.attence_time>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)  AND a.classinfo_id=? INNER JOIN student s ON a.stu_id=s.id LEFT JOIN reasons r ON a.reason_id=r.id   ");
		query.setInteger(0, classinfo_id);
		List<Object[]> list = query.list();
		return list;
	}
	
}